if exists (select 1
            from  sysobjects
           where  id = object_id('bd_temp_schedule_ot')
            and   type = 'U')
   drop table bd_temp_schedule_ot
go

/*==============================================================*/
/* Table: bd_temp_schedule_ot                                   */
/*==============================================================*/
create table bd_temp_schedule_ot (
   user_serial          bigint               not null,
   ot_date              date                 not null,
   shift_id             int                  not null,
   begin_time           datetime             not null,
   end_time             datetime             not null,
   ot_minute            int                  not null,
   creator_id           varchar(50)          not null,
   create_time          datetime             not null default getdate(),
   constraint PK_BD_TEMP_SCHEDULE_OT primary key (user_serial, ot_date, creator_id)
)
go

if exists (select 1 from  sys.extended_properties
           where major_id = object_id('bd_temp_schedule_ot') and minor_id = 0)
begin 
   declare @CurrentUser sysname 
select @CurrentUser = user_name() 
execute sp_dropextendedproperty 'MS_Description',  
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot' 
 
end 


select @CurrentUser = user_name() 
execute sp_addextendedproperty 'MS_Description',  
   '排班加班临时表', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('bd_temp_schedule_ot')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'user_serial')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'user_serial'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '员工ID',
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'user_serial'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('bd_temp_schedule_ot')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'ot_date')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'ot_date'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '加班日期',
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'ot_date'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('bd_temp_schedule_ot')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'shift_id')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'shift_id'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '班次ID',
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'shift_id'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('bd_temp_schedule_ot')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'begin_time')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'begin_time'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '开始时间',
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'begin_time'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('bd_temp_schedule_ot')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'end_time')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'end_time'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '结束时间',
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'end_time'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('bd_temp_schedule_ot')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'ot_minute')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'ot_minute'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '加班时长（分钟）',
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'ot_minute'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('bd_temp_schedule_ot')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'creator_id')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'creator_id'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建人ID',
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'creator_id'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('bd_temp_schedule_ot')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'create_time')
)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'create_time'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '创建时间',
   'user', @CurrentUser, 'table', 'bd_temp_schedule_ot', 'column', 'create_time'
go
